package com.ysstech.reportworld.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ysstech.common.cache.RedisCacheManager;
import com.ysstech.common.entity.*;
import com.ysstech.common.exception.BusinessException;
import com.ysstech.common.service.DicvalueService;
import com.ysstech.common.util.DateUtil;
import com.ysstech.common.util.JdbcUtil;
import com.ysstech.common.util.StringUtil;
import com.ysstech.common.util.fileutil.FileUtil;
import com.ysstech.reportworld.entity.*;
import com.ysstech.reportworld.enums.ReportWorldEnum;
import com.ysstech.reportworld.enums.ReportWorldStatusEnum;
import com.ysstech.reportworld.mapper.TableDetailMapper;
import com.ysstech.reportworld.mapper.TableMapper;
import com.ysstech.reportworld.service.*;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ysstech.reportworld.util.DbConnConfig;
import com.ysstech.reportworld.util.TableInfoUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author lishuangliang
 * @since 2022-04-26
 */
@Slf4j
@Service
public class TableServiceImpl extends ServiceImpl<TableMapper, Table> implements TableService {
    @Autowired
    private TableMapper tableMapper;
    @Autowired
    private TableDetailMapper tableDetailMapper;
    @Autowired
    private DicvalueService dicvalueService;
    @Autowired
    private TableDetailService tableDetailService;
    @Autowired
    private TableService tableService;
    @Autowired
    private DataCustomerService dataCustomerService;
    @Autowired
    private DataModularService dataModularService;
    @Autowired
    private DataReportService dataReportService;
    @Autowired
    private TableIndexedService tableIndexedService;
    @Autowired
    private TableTableService tableTableService;
    @Autowired
    private DbConnConfig dbConnConfig;
    @Autowired
    private CustomerService customerService;
    @Autowired
    private ModularService modularService;
    @Autowired
    private ReportService reportService;
    @Autowired
    private RedisCacheManager redisCacheManager;
    @Autowired
    public DataDemandBugService dataDemandBugService;
    @Autowired
    public DemandBugInfoService demandBugInfoService;
    @Autowired
    private TableStandardCheckService tableStandardCheckService;

    /**
     * 查询所有数据不分页
     *
     * @param table
     * @return
     * @throws Exception
     */
    public List<Table> queryAllReport(Table table) throws Exception {
        Table tableQuery = getQueryWrapper(table);
        return this.switchedListReport(tableMapper.queryReportTable(tableQuery));
    }


    public PageBean<Table> queryAllReport(Table table, PageBean pageBean) throws Exception {
        Table tableQuery = getQueryWrapper(table);
        IPage pageReportTable = tableMapper.queryReportTable(new Page<>(pageBean.getPageNum(), pageBean.getPageSize()), tableQuery);
        this.switchedListReport(pageReportTable.getRecords());
        return new PageBean<>(pageReportTable);
    }

    /**
     * 处理查询条件信息
     *
     * @param table
     * @return
     */
    private Table getQueryWrapper(Table table) {
        if (null == table) {
            return null;
        }
        if (StringUtils.isNotBlank(table.getStrReport())) {
            List<String> ids = JSONArray.parseArray(table.getStrReport(), String.class);
            List<DataReport> list = new ArrayList<>();
            for (String str : ids) {
                DataReport dataReport = new DataReport();
                dataReport.setReportId(str);
                list.add(dataReport);
            }
            table.setListReport(list);
        }
        if (StringUtils.isNotBlank(table.getStrCustomer())) {
            List<String> ids = JSONArray.parseArray(table.getStrCustomer(), String.class);
            List<DataCustomer> list = new ArrayList<>();
            for (String str : ids) {
                DataCustomer dataCustomer = new DataCustomer();
                dataCustomer.setCustomerId(str);
                list.add(dataCustomer);
            }
            table.setListCustomer(list);
        }
        if (StringUtils.isNotBlank(table.getStrModular())) {
            List<String> ids = JSONArray.parseArray(table.getStrModular(), String.class);
            List<DataModular> list = new ArrayList<>();
            for (String str : ids) {
                DataModular dataModular = new DataModular();
                dataModular.setModularId(str);
                list.add(dataModular);
            }
            table.setListModular(list);
        }
        if (StringUtils.isNotBlank(table.getName())) {
            table.setName(table.getName().toUpperCase());
        }
        return table;
    }

    /**
     * 类型，机构等中文的转换
     *
     * @param list
     * @return
     * @throws Exception
     */
    public List<Table> switchedListReport(List<Table> list) throws Exception {
        if (CollectionUtils.isEmpty(list)) {
            return list;
        }
        //状态
        Map<String, String> mapStatus = dicvalueService.getCacheDicValueMap(ReportWorldEnum.REPORT_STATUS.getCode());
        Map<String, String> mapTableLayered = dicvalueService.getCacheDicValueMap(ReportWorldEnum.REPORT_TABLE_LAYERED.getCode());
        // 表类型
        Map<String, String> mapFtype = dicvalueService.getCacheDicValueMap(ReportWorldEnum.REPORT_TABLE_FTYPE.getCode());
        // 表来源
        Map<String, String> maptFsource = dicvalueService.getCacheDicValueMap(ReportWorldEnum.REPORT_TABLE_FSOURCE.getCode());
        for (Table table : list) {
            if (null == table) {
                continue;
            }
            //状态
            if (null != mapStatus && mapStatus.containsKey(String.valueOf(table.getStatus()))) {
                table.setStrStatus(mapStatus.get(String.valueOf(table.getStatus())));
            }
            //报表世界所在分层
            if (null != mapTableLayered && mapTableLayered.containsKey(String.valueOf(table.getLayered()))) {
                table.setStrLayered(mapTableLayered.get(String.valueOf(table.getLayered())));
            }
            //表类型
            if (null != mapFtype && mapFtype.containsKey(String.valueOf(table.getFtype()))) {
                table.setStrFtype(mapFtype.get(String.valueOf(table.getFtype())));
            }
            //表来源
            if (null != maptFsource && maptFsource.containsKey(String.valueOf(table.getFsource()))) {
                table.setStrFsource(maptFsource.get(String.valueOf(table.getFsource())));
            }
            // 所属模块
            table.setModular("");
            if (!CollectionUtils.isEmpty(table.getListModular())) {
                StringBuffer modularId = new StringBuffer();
                StringBuffer modularName = new StringBuffer();
                for (DataModular dataModular : table.getListModular()) {
                    modularId.append(dataModular.getModularId()).append(",");
                    modularName.append(dataModular.getModularName()).append("/");
                }
                table.setStrModular(modularName.deleteCharAt(modularName.length() - 1).toString());
                table.setModular(modularId.deleteCharAt(modularId.length() - 1).toString());
            }
            // 所属客户
            table.setCustomer("");
            if (!CollectionUtils.isEmpty(table.getListCustomer())) {
                StringBuffer customeId = new StringBuffer();
                StringBuffer customeName = new StringBuffer();
                for (DataCustomer dataCustomer : table.getListCustomer()) {
                    customeId.append(dataCustomer.getCustomerId()).append(",");
                    customeName.append(dataCustomer.getCustomerName()).append("/");
                }
                table.setStrCustomer(customeName.deleteCharAt(customeName.length() - 1).toString());
                table.setCustomer(customeId.deleteCharAt(customeId.length() - 1).toString());
            }
            // 所属报表
            table.setReport("");
            if (!CollectionUtils.isEmpty(table.getListReport())) {
                StringBuffer reportId = new StringBuffer();
                StringBuffer reportName = new StringBuffer();
                for (DataReport dataReport : table.getListReport()) {
                    reportId.append(dataReport.getReportId()).append(",");
                    reportName.append(dataReport.getReportName()).append("/");
                }
                table.setStrReport(reportName.deleteCharAt(reportName.length() - 1).toString());
                table.setReport(reportId.deleteCharAt(reportId.length() - 1).toString());
            }
        }
        return list;
    }

    /**
     * 新增表信息
     *
     * @param table
     * @throws Exception
     */
    @Transactional
    public void addReportTable(Table table) throws Exception {
        if (null == table) {
            throw new BusinessException("获取删除报表世界表结构信息失败，请稍后重试！");
        }
        if (StringUtils.isNotBlank(table.getName())) {
            table.setName(table.getName().toUpperCase());
        }
        table.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
        table.setId(DateUtil.generateUUID());
        table.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
        table.setAddTime(LocalDateTime.now());
        this.addTableInfo(table);
        tableStandardCheckService.tableStandardCheckInfo(table, false);
        this.save(table);
        // 修改之前的表状态为已删除
        this.updateOriginalTableName(table, ReportWorldStatusEnum.REPORT_DELETED.getCode());
    }

    /**
     * 保存表关联关系信息
     *
     * @param table
     * @throws Exception
     */
    private void addTableInfo(Table table) throws Exception {
        if (StringUtils.isNotBlank(table.getStrCustomer())) {
            List<String> ids = JSONArray.parseArray(table.getStrCustomer(), String.class);
            List<DataCustomer> list = new ArrayList<>();
            for (String str : ids) {
                DataCustomer dataCustomer = new DataCustomer();
                dataCustomer.setId(DateUtil.generateUUID());
                dataCustomer.setCustomerId(str);
                dataCustomer.setDataId(table.getId());
                dataCustomer.setDataCode(ReportWorldEnum.REPORT_TABLE_CODE.getCode());
                list.add(dataCustomer);
            }
            dataCustomerService.saveBatch(list);
        }
        if (StringUtils.isNotBlank(table.getStrModular())) {
            List<String> ids = JSONArray.parseArray(table.getStrModular(), String.class);
            List<DataModular> list = new ArrayList<>();
            for (String str : ids) {
                DataModular dataModular = new DataModular();
                dataModular.setId(DateUtil.generateUUID());
                dataModular.setModularId(str);
                dataModular.setDataId(table.getId());
                dataModular.setDataCode(ReportWorldEnum.REPORT_TABLE_CODE.getCode());
                list.add(dataModular);
            }
            dataModularService.saveBatch(list);
        }
        if (StringUtils.isNotBlank(table.getStrReport())) {
            List<String> ids = JSONArray.parseArray(table.getStrReport(), String.class);
            List<DataReport> list = new ArrayList<>();
            for (String str : ids) {
                DataReport dataReport = new DataReport();
                dataReport.setId(DateUtil.generateUUID());
                dataReport.setReportId(str);
                dataReport.setDataId(table.getId());
                dataReport.setDataCode(ReportWorldEnum.REPORT_TABLE_CODE.getCode());
                list.add(dataReport);
            }
            dataReportService.saveBatch(list);
        }
    }

    /**
     * 修改表信息 ,同步修改表字段得所属科目，模块，报表信息
     *
     * @param table
     * @throws Exception
     */
    @Transactional
    public void updateReportTable(Table table) throws Exception {
        if (null == table) {
            throw new BusinessException("获取删除报表世界表结构信息失败，请稍后重试！");
        }
        if (StringUtils.isNotBlank(table.getName())) {
            table.setName(table.getName().toUpperCase());
        }
        table.setEditTime(LocalDateTime.now());
        // 修改表信息
        tableStandardCheckService.tableStandardCheckInfo(table, false);
        this.updateById(table);
        //删除表和客户信息关联关系
        QueryWrapper<DataCustomer> wrapperCustomer = new QueryWrapper<>();
        wrapperCustomer.in("DATA_ID", table.getId());
        wrapperCustomer.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
        dataCustomerService.remove(wrapperCustomer);
        //删除表和模块信息关联关系
        QueryWrapper<DataModular> wrapperModular = new QueryWrapper<>();
        wrapperModular.in("DATA_ID", table.getId());
        wrapperModular.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
        dataModularService.remove(wrapperModular);
        //删除表和报表信息关联关系
        QueryWrapper<DataReport> wrapperReport = new QueryWrapper<>();
        wrapperReport.in("DATA_ID", table.getId());
        wrapperReport.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
        dataReportService.remove(wrapperReport);
        // 保存对应关系信息
        this.addTableInfo(table);
        // 修改之前的表状态为已删除
        this.updateOriginalTableName(table, ReportWorldStatusEnum.REPORT_DELETED.getCode());
    }

    /**
     * 增加修改前表的时候，以前的表状态标识为已删除状态
     *
     * @param table
     * @throws Exception
     */
    private void updateOriginalTableName(Table table, Integer status) throws Exception {
        if (null == table || StringUtils.isBlank(table.getId()) || StringUtils.isBlank(table.getOriginalTableName())) {
            return;
        }
        // 查询以前字段的信息修改状态
        QueryWrapper<Table> wrapper = new QueryWrapper<>();
        wrapper.eq("NAME", table.getOriginalTableName());
        Table table1 = this.getOne(wrapper);
        if (null == table1 || StringUtils.isBlank(table1.getId())) {
            throw new BusinessException("获取修改前表信息失败，请稍后重试！");
        }
        table1.setEditUser(redisCacheManager.getLoginUserInfo().getLoginId());
        table1.setEditTime(LocalDateTime.now());
        table1.setStatus(status);
        this.updateById(table1);
    }

    /**
     * 删除表信息
     *
     * @param id
     * @throws Exception
     */
    @Transactional
    public void deleteReportTable(String id) throws Exception {
        if (StringUtils.isBlank(id)) {
            throw new BusinessException("获取删除报表世界表结构信息失败，请稍后重试！");
        }
        // 删除表下面字段的对应信息也要同时删除
        List<String> ids = JSONArray.parseArray(id, String.class);
        this.removeByIds(ids);
        //删除表字段信息
        QueryWrapper<TableDetail> detailQueryWrapper = new QueryWrapper<>();
        detailQueryWrapper.in("TABLE_ID", ids);
        // 删除表字段对应的模块，客户和报表信息
        // 查询删除表的所有字段id 集合
        List<TableDetail> list = tableDetailService.list(detailQueryWrapper);
        if (!CollectionUtils.isEmpty(list)) {
            List<String> idDetail = new ArrayList<>();
            for (TableDetail detail : list) {
                if (null == detail) {
                    continue;
                }
                idDetail.add(detail.getId());
            }
            if (!CollectionUtils.isEmpty(idDetail)) {
                tableDetailService.delReportTableDetail(JSONObject.toJSONString(idDetail));
            }
        }
        tableDetailService.remove(detailQueryWrapper);
        //删除表和客户信息关联关系
        QueryWrapper<DataCustomer> wrapperCustomer = new QueryWrapper<>();
        wrapperCustomer.in("DATA_ID", ids);
        wrapperCustomer.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
        dataCustomerService.remove(wrapperCustomer);
        //删除表和模块信息关联关系
        QueryWrapper<DataModular> wrapperModular = new QueryWrapper<>();
        wrapperModular.in("DATA_ID", ids);
        wrapperModular.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
        dataModularService.remove(wrapperModular);
        //删除表和报表信息关联关系
        QueryWrapper<DataReport> wrapperReport = new QueryWrapper<>();
        wrapperReport.in("DATA_ID", ids);
        wrapperReport.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
        dataReportService.remove(wrapperReport);
        // 表的修改记录，表的关联信息,索引信息
        QueryWrapper<DataDemandBug> updateInfoQueryWrapper = new QueryWrapper<>();
        updateInfoQueryWrapper.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
        updateInfoQueryWrapper.in("DATA_ID", ids);
        dataDemandBugService.remove(updateInfoQueryWrapper);

        QueryWrapper<TableTable> tableTableQueryWrapper = new QueryWrapper<>();
        tableTableQueryWrapper.in("TABLE_ID", ids);
        tableTableService.remove(tableTableQueryWrapper);

        QueryWrapper<TableIndexed> indexedQueryWrapper = new QueryWrapper<>();
        indexedQueryWrapper.in("TABLE_ID", ids);
        tableIndexedService.remove(indexedQueryWrapper);
    }

    /**
     * 获取一张表信息到库里
     *
     * @throws Exception
     */
    @Transactional
    public void readTableInfo(String tableName) throws Exception {
        if (StringUtils.isBlank(tableName)) {
            throw new BusinessException("请在表名处输入要获取的表名信息！");
        }
        Connection conn = dbConnConfig.getInputConnection();
        try {
            List<TableInfo> list = JdbcUtil.getTablesList(conn, tableName.toUpperCase());
            for (TableInfo tableInfo : list) {
                if (null == tableInfo || StringUtils.isBlank(tableInfo.getName())) {
                    continue;
                }
                QueryWrapper<Table> wrapper = new QueryWrapper<>();
                wrapper.eq("NAME", tableInfo.getName());
                Table table = tableService.getOne(wrapper);
                if (null != table) { //表已经存在
                    updateTableInfo(table);
                } else {
                    insertTableInfo(tableInfo.getName());
                }
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException(ex.getMessage());
        } finally {
            JdbcUtil.close(conn);
        }
    }

    /**
     * 表不存在，新增表信息
     *
     * @param tableName
     * @throws Exception
     */
    private void insertTableInfo(String tableName) throws Exception {
        try {
            // 获取这张表的信息
            Map<TableInfo, List<ColumnInfo>> map = JdbcUtil.getTableInfoMap(tableName, dbConnConfig.getInputConnection());
            if (CollectionUtils.isEmpty(map)) {
                throw new BusinessException("数据库没有获取到对应报表的语句用到的表信息，请稍后重试！");
            }
            for (Map.Entry<TableInfo, List<ColumnInfo>> entry : map.entrySet()) {
                TableInfo tableInfo = entry.getKey();
                if (null == tableInfo) {
                    continue;
                }
                Table addTable = new Table();
                String tableId = DateUtil.generateUUID();
                addTable.setId(tableId);
                addTable.setName(tableInfo.getName());
                addTable.setChineseName(tableInfo.getRemarks());
                if (StringUtils.isBlank(tableInfo.getRemarks())) {
                    addTable.setName(tableInfo.getName());
                }
                addTable.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                addTable.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                addTable.setAddTime(LocalDateTime.now());
                //addTable.setLayered(6);
                tableService.save(addTable);
                // 索引处理
                this.insertTableIndexed(tableInfo, tableId);
                if (CollectionUtils.isEmpty(entry.getValue())) {
                    log.error(tableInfo.getName() + "没有获取到对应的字段信息!");
                    continue;
                }
                int i = 1;
                for (ColumnInfo columnInfo : entry.getValue()) {
                    if (null == columnInfo) {
                        continue;
                    }
                    TableDetail tableDetail = TableInfoUtil.getReportTableDetail(columnInfo, tableId);
                    tableDetail.setSequence(i);
                    tableDetail.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                    if (StringUtils.isNotBlank(tableDetail.getColumnDefault())) {
                        tableDetail.setColumnDefault(tableDetailService.getColumnDefault(tableDetail.getColumnDefault().trim()));
                    }
                    tableDetail.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    tableDetail.setAddTime(LocalDateTime.now());
                    tableDetailService.save(tableDetail);
                    i++;
                }
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException("新增表信息失败，请稍后重试！" + ex.getMessage());
        }
    }

    /**
     * 表结构已经存在更新表结构信息
     *
     * @param table
     * @throws Exception
     */
    private void updateTableInfo(Table table) throws Exception {
        try {
            // 获取这张表的信息
            Map<TableInfo, List<ColumnInfo>> map = JdbcUtil.getTableInfoMap(table.getName(), dbConnConfig.getInputConnection());
            if (CollectionUtils.isEmpty(map)) {
                throw new BusinessException("数据库没有获取到对应报表的语句用到的表信息，请稍后重试！");
            }
            for (Map.Entry<TableInfo, List<ColumnInfo>> entry : map.entrySet()) {
                TableInfo tableInfo = entry.getKey();
                if (null == tableInfo) {
                    continue;
                }
                Table addTable = new Table();
                String tableId = table.getId();
                addTable.setId(tableId);
                addTable.setName(tableInfo.getName());
                addTable.setChineseName(tableInfo.getRemarks());
                if (StringUtils.isBlank(tableInfo.getRemarks())) {
                    addTable.setName(tableInfo.getName());
                }
                addTable.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                //addTable.setLayered(6);
                addTable.setEditUser(redisCacheManager.getLoginUserInfo().getLoginId());
                addTable.setEditTime(LocalDateTime.now());
                tableService.updateById(addTable);
                // 索引处理
                this.insertTableIndexed(tableInfo, tableId);
                if (CollectionUtils.isEmpty(entry.getValue())) {
                    log.error(tableInfo.getName() + "没有获取到对应的字段信息!");
                    continue;
                }
                int i = 1;
                // 删除所有字段信息
              /*  QueryWrapper<TableDetail> wrapperCloum = new QueryWrapper<>();
                wrapperCloum.eq("TABLE_ID", tableId);
                tableDetailService.remove(wrapperCloum);*/
                // 获取库里的所有字段
                for (ColumnInfo columnInfo : entry.getValue()) {
                    if (null == columnInfo) {
                        continue;
                    }
                    QueryWrapper<TableDetail> wrapper2 = new QueryWrapper<>();
                    wrapper2.eq("COLUMN_NAME", columnInfo.getName());
                    wrapper2.eq("TABLE_ID", tableId);
                    TableDetail reportWorldTableDetail = tableDetailService.getOne(wrapper2);
                    if (null != reportWorldTableDetail) { // 已经存在该字段了
                        TableDetail tableDetail = TableInfoUtil.getReportTableDetail(columnInfo, tableId);
                        tableDetail.setId(reportWorldTableDetail.getId());
                        tableDetail.setSequence(i);
                        tableDetail.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                        if (StringUtils.isNotBlank(tableDetail.getColumnDefault())) {
                            tableDetail.setColumnDefault(tableDetailService.getColumnDefault(tableDetail.getColumnDefault().trim()));
                        }
                        tableDetail.setEditUser(redisCacheManager.getLoginUserInfo().getLoginId());
                        tableDetail.setEditTime(LocalDateTime.now());
                        tableDetailService.updateById(tableDetail);
                    } else {
                        TableDetail tableDetail = TableInfoUtil.getReportTableDetail(columnInfo, tableId);
                        tableDetail.setSequence(i);
                        tableDetail.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                        if (StringUtils.isNotBlank(tableDetail.getColumnDefault())) {
                            tableDetail.setColumnDefault(tableDetailService.getColumnDefault(tableDetail.getColumnDefault().trim()));
                        }
                        tableDetail.setEditUser(redisCacheManager.getLoginUserInfo().getLoginId());
                        tableDetail.setEditTime(LocalDateTime.now());
                        tableDetailService.save(tableDetail);
                    }
                    i++;
                }
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException(ex.getMessage());
        }
    }

    /**
     * 插入表索引信息
     *
     * @throws Exception
     */
    private void insertTableIndexed(TableInfo tableInfo, String tableId) throws Exception {
        if (null == tableInfo || StringUtils.isBlank(tableId)) {
            return;
        }
        QueryWrapper<TableIndexed> wrapper = new QueryWrapper<>();
        //删除表的索引信息
        wrapper.eq("TABLE_ID", tableId);
        tableIndexedService.remove(wrapper);
        //重新插入
        List<IndexInfo> list = tableInfo.getListIndexInfo();
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        List<TableIndexed> tableIndexeds = new ArrayList<>();
        for (IndexInfo info : list) {
            if (null == info) {
                continue;
            }
            TableIndexed worldTableIndexed = new TableIndexed();
            worldTableIndexed.setId(DateUtil.generateUUID());
            worldTableIndexed.setTableId(tableId);
            worldTableIndexed.setIndexedName(info.getIndexName());
            worldTableIndexed.setIndexedColumnName(info.getColumnName());
            // 索引列排序
            String indexAscOrDesc = ("A".equals(info.getAscOrDesc())) ? "ASC" : ("B".equals(info.getAscOrDesc())) ? "DESC" : "";
            worldTableIndexed.setIndexedAscDesc(indexAscOrDesc);
            if (StringUtils.isBlank(info.getIndexType())) {
                worldTableIndexed.setIndexedType(1);
            } else {
                worldTableIndexed.setIndexedType(Integer.valueOf(info.getIndexType()));
            }
            worldTableIndexed.setIndexedColumnSequence(Integer.valueOf(info.getOrdinalPosition()));
            tableIndexeds.add(worldTableIndexed);
        }
        tableIndexedService.saveBatch(tableIndexeds);
    }

    /**
     * 批量修改表信息
     *
     * @param table
     */
    public void batchUpdateTableInfo(Table table, String ids) throws Exception {
        try {
            if (null == table || StringUtils.isBlank(ids)) {
                throw new BusinessException("获取批量修改数据信息失败，请稍后重试！");
            }
            List<String> listId = JSONArray.parseArray(ids, String.class);
            if (CollectionUtils.isEmpty(listId)) {
                throw new BusinessException("获取批量修改数据信息失败，请稍后重试！");
            }
            for (String id : listId) {
                if (StringUtils.isBlank(id)) {
                    continue;
                }
                table.setId(id);
                // 批量修改了对应的模块 客户 报表，就先删除在插入
                if (StringUtils.isNotBlank(table.getStrCustomer())) {
                    //删除表和客户信息关联关系
                    QueryWrapper<DataCustomer> wrapperCustomer = new QueryWrapper<>();
                    wrapperCustomer.in("DATA_ID", table.getId());
                    wrapperCustomer.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
                    dataCustomerService.remove(wrapperCustomer);
                }
                if (StringUtils.isNotBlank(table.getStrModular())) {
                    //删除表和模块信息关联关系
                    QueryWrapper<DataModular> wrapperModular = new QueryWrapper<>();
                    wrapperModular.in("DATA_ID", table.getId());
                    wrapperModular.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
                    dataModularService.remove(wrapperModular);
                }
                if (StringUtils.isNotBlank(table.getStrReport())) {
                    //删除表和报表信息关联关系
                    QueryWrapper<DataReport> wrapperReport = new QueryWrapper<>();
                    wrapperReport.in("DATA_ID", table.getId());
                    wrapperReport.eq("DATA_CODE", ReportWorldEnum.REPORT_TABLE_CODE.getCode());
                    dataReportService.remove(wrapperReport);
                }
                this.addTableInfo(table);  // 保存表字段对应的客户，模块，报表信息
                Table tableUpdate = new Table();
                UpdateWrapper<Table> wrapper = new UpdateWrapper<>();
                wrapper.eq("ID", id);
                boolean falg = false;
                if (null != table.getStatus()) {
                    tableUpdate.setStatus(table.getStatus());
                    falg = true;
                }
                if (null != table.getLayered()) {
                    tableUpdate.setLayered(table.getLayered());
                    falg = true;
                }
                if (StringUtils.isNotBlank(table.getVersion())) {
                    tableUpdate.setVersion(table.getVersion());
                    falg = true;
                }
                if (null != table.getFtype()) {
                    tableUpdate.setFtype(table.getFtype());
                    falg = true;
                }
                if (null != table.getFsource()) {
                    tableUpdate.setFsource(table.getFsource());
                    falg = true;
                }
                if (StringUtils.isNotBlank(table.getDescription())) {
                    tableUpdate.setDescription(table.getDescription());
                    falg = true;
                }
                if (falg) {
                    tableUpdate.setEditUser(redisCacheManager.getLoginUserInfo().getLoginId());
                    tableUpdate.setEditTime(LocalDateTime.now());
                    this.update(tableUpdate, wrapper);
                }
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException(ex.getMessage());
        }
    }

    /**
     * 读取表结构模板信息
     *
     * @throws Exception
     */
    public String getTableInfoExecl(MultipartFile multipartfile) throws Exception {
        try {
            String fileName = multipartfile.getOriginalFilename();
            fileName = fileName.substring(0, fileName.lastIndexOf("."));
            if (!StringUtil.oneOf(fileName, "表结构模板")) {
                throw new BusinessException("请选择正确的表结构模板文件！");

            }
            return this.tableMouldFile(multipartfile); // 表模板文件读取
        } catch (Exception ex) {
            log.error("读取文件信息失败！" + ex.getMessage());
            throw new BusinessException("读取文件信息失败！" + ex.getMessage());
        }
    }

    /**
     * 和文件你表对比差异
     *
     * @throws Exception
     */
    public String magerTableByFile(MultipartFile multipartfile) throws Exception {
        try {
            String fileName = multipartfile.getOriginalFilename();
            fileName = fileName.substring(0, fileName.lastIndexOf("."));
            if (!StringUtil.oneOf(fileName, "表结构对比")) {
                throw new BusinessException("请选择正确的表结构模板文件！");

            }
            return this.standardLayeredFile(multipartfile); // 对比标准层差异

        } catch (Exception ex) {
            log.error("读取文件信息失败！" + ex.getMessage());
            throw new BusinessException("读取文件信息失败！" + ex.getMessage());
        }
    }

    /**
     * 读取设计文档  同步字段和业务主键
     */
    public String readDemandDesign(MultipartFile multipartfile) throws Exception {
        StringBuffer buffer = new StringBuffer();
        try {
            String fileName = multipartfile.getOriginalFilename();
            fileName = fileName.substring(0, fileName.lastIndexOf("."));
            if (!StringUtil.oneOf(fileName, "设计文档")) {
                throw new BusinessException("请选择正确的设计文档！");

            }
            List<List<Object>> listSize = FileUtil.readerExcelObj(FileUtil.MultipartFileToFile(multipartfile), "报表目录");
            if (CollectionUtils.isEmpty(listSize) || listSize.size() <= 2) {
                throw new BusinessException("没有获取导报表目录的sheet页信息！");
            }
            for (int k = 2; k < listSize.size(); k++) {
                List<List<Object>> list = FileUtil.readerExcelObj(FileUtil.MultipartFileToFile(multipartfile), k);
                if (CollectionUtils.isEmpty(list)) {
                    log.error("文件中没有获取到数据！");
                    continue;
                }
                String tableName = "";
                Map<Integer, TableDetail> map = new HashMap();
                for (int m = 0; m < list.size(); m++) {
                    List<Object> objects = list.get(m);
                    if (null == objects.get(0) || StringUtils.isBlank(objects.get(0).toString())) {
                        continue;
                    }
                    if (StringUtil.oneOf(objects.get(0).toString(), "落地表")) {
                        tableName = objects.get(1).toString();
                    }
                    if (StringUtil.oneOf(objects.get(0).toString(), "字段名")) {
                        QueryWrapper<Table> wrapper = new QueryWrapper<>();
                        wrapper.eq("NAME", tableName);
                        Table table = tableService.getOne(wrapper);
                        if (null == table) {
                            buffer.append(tableName).append("系统种没有获取到表结构信息！").append("\r\n");
                            break;
                        }
                        for (int i = 1; i < objects.size(); i++) {
                            if (null == objects.get(i) || StringUtils.isBlank(objects.get(i).toString())) {
                                continue;
                            }
                            String columName = objects.get(i).toString();
                            QueryWrapper<TableDetail> wrapper2 = new QueryWrapper<>();
                            wrapper2.eq("COLUMN_NAME", columName);
                            wrapper2.eq("TABLE_ID", table.getId());
                            TableDetail tableDetail = tableDetailService.getOne(wrapper2);
                            if (null == tableDetail) {
                                buffer.append(tableName).append("字段" + columName).append("系统种没有获取到字段信息！").append("\r\n");
                            }
                            map.put(i, tableDetail);
                        }
                        continue;
                    }
                    if (StringUtil.oneOf(objects.get(0).toString(), "业务主键") && !CollectionUtils.isEmpty(map)) {
                        for (int i = 1; i < objects.size(); i++) {
                            if (null == objects.get(i) || StringUtils.isBlank(objects.get(i).toString())) {
                                continue;
                            }
                            String columName = objects.get(i).toString();
                            TableDetail tableDetail = map.get(i);
                            if (null == tableDetail) {
                                continue;
                            }
                            if ("Y".equals(objects.get(i).toString())) {
                                tableDetail.setBusinessPrimaryKey(1);
                            } else {
                                tableDetail.setBusinessPrimaryKey(2);
                            }
                        }
                        continue;
                    }
                    if (StringUtil.oneOf(objects.get(0).toString(), "字典") && !CollectionUtils.isEmpty(map)) {
                        for (int i = 1; i < objects.size(); i++) {
                            if (null == objects.get(i) || StringUtils.isBlank(objects.get(i).toString())) {
                                continue;
                            }
                            TableDetail tableDetail = map.get(i);
                            if (null == tableDetail) {
                                continue;
                            }
                            tableDetail.setCodeName(objects.get(i).toString());
                        }
                        break;
                    }
                }
                if (CollectionUtils.isEmpty(map)) {
                    continue;
                }
                for (Map.Entry<Integer, TableDetail> entry : map.entrySet()) {
                    TableDetail tableDetail = entry.getValue();
                    if (null == tableDetail) {
                        continue;
                    }
                    tableDetailService.updateById(tableDetail);
                }
                buffer.append(tableName).append(map.size() + "条数据！").append("\r\n");
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException("更新表业务主键和字典信息失败！" + ex.getMessage());
        }
        return buffer.toString();
    }

    /* *
     * 读取标准层文档里的表结构信息
     *
     * @param multipartfile
     * @throws Exception
     */
    private String standardLayeredFile(MultipartFile multipartfile) throws Exception {
        List<List<Object>> list = FileUtil.readerExcelObj(FileUtil.MultipartFileToFile(multipartfile));
        if (CollectionUtils.isEmpty(list)) {
            log.error("文件中没有获取到数据！");
            return null;
        }
        Map<String, List<TableDetail>> map = new LinkedHashMap<>();
        String name = "", tableName = "";
        try {
            for (int i = 0; i < list.size(); i++) {
                List<Object> objects = list.get(i);
                if (0 == i || CollectionUtils.isEmpty(objects)
                        || null == objects.get(1) || StringUtils.isBlank(objects.get(1).toString())) {
                    continue;
                }
                TableDetail detail = new TableDetail();
                detail.setColumnName(objects.get(1).toString());
                detail.setColumnChineseName(objects.get(2).toString());
                name = detail.getColumnName();
                tableName = objects.get(0).toString();
                String decimal = "";
                if (null != objects.get(3)) {
                    String type = objects.get(3).toString();
                    if (type.indexOf("(") > -1) {
                        String length = type.substring(type.indexOf("(") + 1, type.indexOf(")"));
                        if (length.indexOf(",") > -1) {
                            decimal = length.substring(length.indexOf(",") + 1);
                            length = length.substring(0, length.indexOf(","));
                        }
                        if (StringUtils.isNotBlank(length)) {
                            detail.setColumnLength(Integer.valueOf(length.trim()));
                        }
                        if (StringUtils.isNotBlank(decimal)) {
                            detail.setColumnDecimal(Integer.valueOf(decimal.trim()));
                        }
                    }
                    if (StringUtil.oneOf(type.toUpperCase(), "VARCHAR2")) {
                        detail.setColumnType(1);
                    } else if (StringUtil.oneOf(type.toUpperCase(), "NUMBER")) {
                        detail.setColumnType(2);
                        if (StringUtils.isNotBlank(decimal)) {
                            detail.setColumnType(3);
                        }
                    } else if (StringUtil.oneOf(type.toUpperCase(), "CLOB")) {
                        detail.setColumnType(4);
                    } else if (StringUtil.oneOf(type.toUpperCase(), "TIMESTAMP")) {
                        detail.setColumnType(5);
                    } else if (StringUtil.oneOf(type.toUpperCase(), "DATE")) {
                        detail.setColumnType(6);
                    } else if (StringUtil.oneOf(type.toUpperCase(), "CHAR") && !StringUtil.oneOf(type.toUpperCase(), "VARCHAR2")) {
                        detail.setColumnType(10);
                    } else if (StringUtil.oneOf(type.toUpperCase(), "V") && !StringUtil.oneOf(type.toUpperCase(), "VARCHAR2")) {
                        detail.setColumnType(1);
                    }

                }
                detail.setColumnNull(1);
                if (null != objects.get(5)) {
                    String defaultValue = objects.get(5).toString();
                    detail.setColumnDefault(defaultValue);
                }
                if (null != objects.get(4)) {
                    String codeName = objects.get(4).toString();
                    if (StringUtils.isNotBlank(codeName) && codeName.indexOf("-") > -1) {
                        detail.setCodeName(codeName.substring(0, codeName.indexOf("-")));
                    }
                }
                if (null != objects.get(6)) {
                    String codeName = objects.get(6).toString();
                    if ("Y（业务主键）".equals(codeName)) {
                        detail.setBusinessPrimaryKey(1);
                    }
                    if ("Y（技术主键）".equals(codeName)) {
                        detail.setPrimaryKey(1);
                    }
                }
                if (map.containsKey(objects.get(0).toString())) {
                    List<TableDetail> detailList = map.get(objects.get(0).toString());
                    detailList.add(detail);
                    map.put(objects.get(0).toString(), detailList);
                } else {
                    List<TableDetail> detailList = new ArrayList<>();
                    detailList.add(detail);
                    map.put(objects.get(0).toString(), detailList);
                }
            }
        } catch (Exception ex) {
            log.error(ex.getMessage() + tableName + name);
        }
        return this.updateReportWorldTableInfo(map);
    }


    /* * 读取需求文档表格信息到数据库
     *
     * @param map
     * @throws Exception
     */
    private String updateReportWorldTableInfo(Map<String, List<TableDetail>> map) throws Exception {
        if (CollectionUtils.isEmpty(map)) {
            throw new BusinessException("文件中没有获取到表信息！");
        }
        StringBuffer buffer = new StringBuffer();
        int i = 0;
        String detialName = "";
        for (Map.Entry<String, List<TableDetail>> entry : map.entrySet()) {
            if (StringUtils.isBlank(entry.getKey()) || CollectionUtils.isEmpty(entry.getValue())) {
                continue;
            }
            QueryWrapper<Table> wrapper = new QueryWrapper<>();
            String tableName = entry.getKey();
            wrapper.eq("NAME", tableName);
            Table table = tableService.getOne(wrapper);
            if (null == table) {  //表不存在
                buffer.append("标准层文档里存在表" + tableName).append("数据平台不存在表结构信息").append("\r\n");
                continue;
            }
            try {
                Map<String, String> map2 = new HashMap<>();
                for (TableDetail detail : entry.getValue()) {
                    if (null == detail || StringUtils.isBlank(detail.getColumnName())) {
                        continue;
                    }
                    i++;
                    detialName = detail.getColumnName();
                    QueryWrapper<TableDetail> wrapper2 = new QueryWrapper<>();
                    wrapper2.eq("COLUMN_NAME", detail.getColumnName());
                    wrapper2.eq("TABLE_ID", table.getId());
                    TableDetail tableDetail = tableDetailService.getOne(wrapper2);
                    if (null == tableDetail) {
                        log.error("字段不存在" + table.getName() + detail.getColumnName());
                        buffer.append("标准层文档表" + tableName).append("存在字段" + detail.getColumnName()).append("数据平台不存在该字段").append("\r\n");
                        continue;
                    }
                    if (StringUtils.isNotBlank(detail.getColumnChineseName()) && !detail.getColumnChineseName().equals(tableDetail.getColumnChineseName())) {
                        buffer.append("标准层文档表" + tableName).append("字段" + detail.getColumnName()).append("中文名称" + detail.getColumnChineseName())
                                .append("数据平台中文名称").append(tableDetail.getColumnChineseName() + "不一致").append("\r\n");
                    }
                    if (null != detail.getColumnType() && detail.getColumnType() != tableDetail.getColumnType()) {
                        if ((3 == detail.getColumnType() && 2 == tableDetail.getColumnType()) || (2 == detail.getColumnType() && 3 == tableDetail.getColumnType())) {
                            continue;
                        }
                        buffer.append("标准层文档表" + tableName).append("字段" + detail.getColumnName()).append("字段类型" + detail.getColumnType())
                                .append("数据平台字段类型").append(tableDetail.getColumnType() + "不一致").append("\r\n");
                    }
                    if (null != detail.getColumnLength() && !detail.getColumnLength().equals(tableDetail.getColumnLength())) {
                        if (0 == detail.getColumnLength() && null == tableDetail.getColumnLength()) {
                            continue;
                        }
                        buffer.append("标准层文档表" + tableName).append("字段" + detail.getColumnName()).append("字段长度" + detail.getColumnLength())
                                .append("数据平台字段长度").append(tableDetail.getColumnLength() + "不一致").append("\r\n");
                    }
                    if (null != detail.getColumnDecimal() && detail.getColumnDecimal() != tableDetail.getColumnDecimal()) {
                        buffer.append("标准层文档表" + tableName).append("字段" + detail.getColumnName()).append("字段小数位数" + detail.getColumnDecimal())
                                .append("数据平台字段小数位数").append(tableDetail.getColumnDecimal() + "不一致").append("\r\n");
                    }
                    if (StringUtils.isNotBlank(detail.getCodeName()) && !detail.getCodeName().equals(tableDetail.getCodeName())) {
                        buffer.append("标准层文档表" + tableName).append("字段" + detail.getColumnName()).append("字段字典值" + detail.getCodeName())
                                .append("数据平台字段字典值").append(tableDetail.getCodeName() + "不一致").append("\r\n");
                    }
                    if (StringUtils.isNotBlank(detail.getColumnDefault()) && !detail.getColumnDefault().equals(tableDetail.getColumnDefault())) {
                        buffer.append("标准层文档表" + tableName).append("字段" + detail.getColumnName()).append("字段默认值" + detail.getColumnDefault())
                                .append("数据平台字段默认值").append(tableDetail.getColumnDefault() + "不一致").append("\r\n");
                    }
                    map2.put(detail.getColumnName(), detail.getColumnName());
                }
                // 获取库的所有字段
                QueryWrapper<TableDetail> wrapper2 = new QueryWrapper<>();
                wrapper2.eq("TABLE_ID", table.getId());
                wrapper2.eq("STATUS", 1);
                List<TableDetail> list = tableDetailService.list(wrapper2);
                for (TableDetail detail : list) {
                    if (map2.containsKey(detail.getColumnName())) {
                        continue;
                    }
                    if (StringUtil.oneCase(detail.getColumnName(), "FILENAME,FVERSION,ENTRUST_ORGAN," +
                            "CREATENAME,INSERTTIME")) {
                        continue;
                    }
                    buffer.append("数据平台" + tableName).append("字段" + detail.getColumnName())
                            .append("标准层文档中不存在该字段").append("\r\n");
                }
            } catch (Exception ex) {
                log.error(ex.getMessage() + tableName + detialName);
            }
        }
        return buffer.toString();
    }

    /**
     * 读取表结构模板
     *
     * @param multipartfile
     * @throws Exception
     */
    @Transactional
    public String tableMouldFile(MultipartFile multipartfile) throws Exception {
        if (null == multipartfile) {
            throw new BusinessException("没有获取到文件，请稍后重试！");
        }
        String fileName = multipartfile.getOriginalFilename();
        fileName = fileName.substring(0, fileName.lastIndexOf("."));
        if (!StringUtil.oneOf(fileName, "表结构模板")) {
            throw new BusinessException("请选择正确的基础数据模板表文件！");
        }
        // 上传文件之前，先删除这次文件上传的所有数据
        // 删除表
        QueryWrapper<Table> tableQueryWrapper = new QueryWrapper();
        tableQueryWrapper.eq("FILE_NAME", fileName);
        tableService.remove(tableQueryWrapper);
        // 删除表明细信息
        QueryWrapper<TableDetail> detailQueryWrapper = new QueryWrapper();
        detailQueryWrapper.eq("FILE_NAME", fileName);
        tableDetailService.remove(detailQueryWrapper);
        // 删除表对应的模块
        QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
        modularQueryWrapper.eq("FILE_NAME", fileName);
        dataModularService.remove(modularQueryWrapper);
        // 删除表对应的报表
        QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
        reportQueryWrapper.eq("FILE_NAME", fileName);
        dataReportService.remove(reportQueryWrapper);
        // 删除表对应的客户
        QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
        modularQueryWrapper.eq("FILE_NAME", fileName);
        dataCustomerService.remove(customerQueryWrapper);
        // 删除表对应的索引
        QueryWrapper<TableIndexed> indexedQueryWrapper = new QueryWrapper<>();
        indexedQueryWrapper.eq("FILE_NAME", fileName);
        tableIndexedService.remove(indexedQueryWrapper);
        // 删除需求BUG对应关系
        QueryWrapper<DataDemandBug> dataDemandBugQueryWrapper = new QueryWrapper<>();
        dataDemandBugQueryWrapper.eq("FILE_NAME", fileName);
        dataDemandBugService.remove(dataDemandBugQueryWrapper);
        StringBuffer buffer = new StringBuffer();
        // 获取现在最大的版本号
        String version = dicvalueService.getMaxValue(ReportWorldEnum.REPORT_VERSION.getCode());
        if (StringUtils.isBlank(version)) {
            throw new BusinessException("没有获取到版本号，请稍后重试！");
        }
        // 查询当前版本的所有需求BUG
        QueryWrapper<DemandBugInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("VERSION", version);
        List<DemandBugInfo> listDemandBugInfo = demandBugInfoService.list(queryWrapper);
        Map<String, String> map = new HashMap<>();
        if (!CollectionUtils.isEmpty(listDemandBugInfo)) {
            for (DemandBugInfo demandBugInfo : listDemandBugInfo) {
                if (null == demandBugInfo) {
                    continue;
                }
                map.put(demandBugInfo.getDemandBugNum(), demandBugInfo.getId());
            }
        }
        // 读取表信息的sheet
        this.mergeTableInfo(multipartfile, fileName, buffer, map);
        // 读取表字段信息的sheet
        this.mergeTableColumnInfo(multipartfile, fileName, buffer, map);
        return buffer.toString();
    }

    /**
     * 读取文件处理表信息到数据库
     *
     * @return
     * @throws Exception
     */
    private void mergeTableInfo(MultipartFile multipartfile, String fileName, StringBuffer buffer, Map<String, String> map) throws Exception {
        // 获取现在最大的版本号
        String version = dicvalueService.getMaxValue(ReportWorldEnum.REPORT_VERSION.getCode());
        if (StringUtils.isBlank(version)) {
            buffer.append("没有获取到版本号，请检查版本系统版本号在上传数据").append("\r\n");
            return;
        }
        // 表信息得sheet 没有数据就返回
        List<List<Object>> listTableInfo = FileUtil.readerExcelObj(FileUtil.MultipartFileToFile(multipartfile), "TABLE_INFO");
        if (CollectionUtils.isEmpty(listTableInfo)) {
            buffer.append("没有需要处理的表信息").append("\r\n");
            return;
        }
        // 循环表信息
        for (int i = 0; i < listTableInfo.size(); i++) {
            List<Object> objects = listTableInfo.get(i);
            if (0 == i) {
                continue;
            }
            if (CollectionUtils.isEmpty(objects) || null == objects.get(0) || StringUtils.isBlank(objects.get(0).toString())) {
                buffer.append("表信息的" + i + "行，没有表名信息请检查后重新上传！").append("\r\n");
                continue;
            }
            QueryWrapper<Table> wrapper = new QueryWrapper<>();
            wrapper.eq("NAME", objects.get(0).toString());
            Table table = tableService.getOne(wrapper);
            if (null == table) {  //表不存在
                //新增表
                Table tableadd = new Table();
                String tableId = DateUtil.generateUUID();
                tableadd.setId(tableId);
                tableadd.setName(objects.get(0).toString()); //表名
                if (null == objects.get(1) || StringUtils.isBlank(objects.get(1).toString())) {
                    buffer.append("表信息的" + i + "行，没有表中文名称，请检查后重新上传！").append("\r\n");
                    continue;
                }
                tableadd.setChineseName(objects.get(1).toString()); //表中文名称
                if (null != objects.get(2) && StringUtils.isNotBlank(objects.get(2).toString())) {
                    tableadd.setDescription(objects.get(2).toString());
                }
                String layered = null;
                if (null != objects.get(3) && StringUtils.isNotBlank(objects.get(3).toString())) { //所在分层
                    layered = dicvalueService.getDicValue(ReportWorldEnum.REPORT_TABLE_LAYERED.getCode(), objects.get(3).toString());
                    tableadd.setLayered(Integer.valueOf(layered));
                }
                tableadd.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                tableadd.setFileName(fileName);
                tableadd.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                tableadd.setAddTime(LocalDateTime.now());
                tableadd.setVersion(version);
                tableService.save(tableadd);
                // 插入对应的模块，客户，报表信息
                this.insertModularCustomerReport(tableId, fileName, objects, false, tableId, false, map);
                continue;
            }
            // 表中文名称
            if (null == objects.get(1) || StringUtils.isBlank(objects.get(1).toString())) {
                buffer.append("表信息的" + i + "行，没有表中文名称，请检查后重新上传！").append("\r\n");
                continue;
            }
            table.setChineseName(objects.get(1).toString());
            if (null != objects.get(2) && StringUtils.isNotBlank(objects.get(2).toString())) {
                table.setDescription(objects.get(2).toString());
            }
            String layered = null;
            if (null != objects.get(3)) { //所在分层
                layered = dicvalueService.getDicValue(ReportWorldEnum.REPORT_TABLE_LAYERED.getCode(), objects.get(2).toString());
            }
            table.setLayered(Integer.valueOf(layered));
            table.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
            table.setEditUser(redisCacheManager.getLoginUserInfo().getLoginId());
            table.setEditTime(LocalDateTime.now());
            table.setVersion(version);
            tableService.updateById(table);
            // 插入对应的模块，客户，报表信息
            this.insertModularCustomerReport(table.getId(), fileName, objects, true, table.getId(), false, map);
        }
        buffer.append("成功处理表信息" + (listTableInfo.size() - 1) + "条").append("\r\n");
    }

    /**
     * 读取文件处理表字段信息到数据库
     *
     * @return
     * @throws Exception
     */
    private void mergeTableColumnInfo(MultipartFile multipartfile, String fileName, StringBuffer buffer, Map<String, String> map) throws Exception {
        // 获取现在最大的版本号
        String version = dicvalueService.getMaxValue(ReportWorldEnum.REPORT_VERSION.getCode());
        if (StringUtils.isBlank(version)) {
            buffer.append("没有获取到版本号，请检查版本系统版本号在上传数据").append("\r\n");
            return;
        }
        // 字段的sheet
        List<List<Object>> listColumnInfo = FileUtil.readerExcelObj(FileUtil.MultipartFileToFile(multipartfile), "TABLE_COLUMN_INFO");
        if (CollectionUtils.isEmpty(listColumnInfo)) {
            buffer.append("没有需要处理的表明细信息").append("\r\n");
            return;
        }
        Map<String, String> tableIdMap = new HashMap<>();
        int k = 1;
        for (int i = 0; i < listColumnInfo.size(); i++) {
            if (0 == i) {
                continue;
            }
            List<Object> objects = listColumnInfo.get(i);
            if (CollectionUtils.isEmpty(objects) || null == objects.get(0) || StringUtils.isBlank(objects.get(0).toString())
                    || null == objects.get(1) || StringUtils.isBlank(objects.get(1).toString())) {
                buffer.append("表明细信息的" + i + "行，没有表名或者英文字段名称，请检查后重新上传！").append("\r\n");
                continue;
            }
            TableDetail detail = new TableDetail();
            detail.setColumnName(objects.get(1).toString()); //字段名称
            if (null == objects.get(3) || StringUtils.isBlank(objects.get(3).toString())) { //字段中文名
                buffer.append("表明细信息的" + i + "行，没有字段中文名，请检查后重新上传！").append("\r\n");
                continue;
            }
            detail.setColumnChineseName(objects.get(3).toString());
            String decimal = "";
            if (null == objects.get(2) || StringUtils.isBlank(objects.get(2).toString())) {
                buffer.append("表明细信息的" + i + "行，没有字段长度，请检查后重新上传！").append("\r\n");
                continue;
            }
            if (!tableIdMap.containsKey(objects.get(0).toString())) {
                QueryWrapper<Table> wrapper = new QueryWrapper<>();
                wrapper.eq("NAME", objects.get(0).toString());
                Table table = tableService.getOne(wrapper);
                if (null == table) {
                    buffer.append("表明细信息的" + i + "行，库里还没有该表名，请检查后重新上传！").append("\r\n");
                    continue;
                }
                tableIdMap.put(table.getName(), table.getId());
                k = 1; //一个新表从1开始
            }
            // 字段长度
            String type = objects.get(2).toString();
            if (type.indexOf("(") > -1) {
                String length = type.substring(type.indexOf("(") + 1, type.indexOf(")"));
                if (length.indexOf(",") > -1) {
                    decimal = length.substring(length.indexOf(",") + 1);
                    length = length.substring(0, length.indexOf(","));
                }
                if (StringUtils.isNotBlank(length)) {
                    detail.setColumnLength(Integer.valueOf(length.trim()));
                }
                if (StringUtils.isNotBlank(decimal)) {
                    detail.setColumnDecimal(Integer.valueOf(decimal.trim()));
                }
            }
            if (StringUtil.oneOf(type.toUpperCase(), "VARCHAR2")) {
                detail.setColumnType(1);
            } else if (StringUtil.oneOf(type.toUpperCase(), "NUMBER")) {
                detail.setColumnType(2);
                if (StringUtils.isNotBlank(decimal)) {
                    detail.setColumnType(3);
                }
            } else if (StringUtil.oneOf(type.toUpperCase(), "CLOB")) {
                detail.setColumnType(4);
            } else if (StringUtil.oneOf(type.toUpperCase(), "TIMESTAMP")) {
                detail.setColumnType(5);
            } else if (StringUtil.oneOf(type.toUpperCase(), "DATE")) {
                detail.setColumnType(6);
            } else if (StringUtil.oneOf(type.toUpperCase(), "CHAR") && !StringUtil.oneOf(type.toUpperCase(), "VARCHAR2")) {
                detail.setColumnType(10);
            } else if (StringUtil.oneOf(type.toUpperCase(), "V") && !StringUtil.oneOf(type.toUpperCase(), "VARCHAR2")) {
                detail.setColumnType(1);
            }
            if (null != objects.get(4) && StringUtils.isNotBlank(objects.get(4).toString())) { //字段描述
                detail.setColumnDescribe(objects.get(4).toString());
            }
            if (null != objects.get(5) && StringUtils.isNotBlank(objects.get(5).toString())) { //字典
                String codeName = objects.get(5).toString();
                detail.setCodeName(objects.get(5).toString());
                if (StringUtils.isNotBlank(codeName) && codeName.indexOf("-") > -1) {
                    detail.setCodeName(codeName.substring(0, codeName.indexOf("-")));
                }
            }
            detail.setColumnNull(1);
            if (null != objects.get(6) && "N".equals(objects.get(6).toString())) { //是否为空
                detail.setColumnNull(2);
            }
            detail.setPrimaryKey(2);
            if (null != objects.get(7) && "Y".equals(objects.get(7).toString())) { //主键
                detail.setPrimaryKey(1);
            }
            if (null != objects.get(8) && StringUtils.isNotBlank(objects.get(8).toString())) { //默认值
                detail.setColumnDefault(tableDetailService.getColumnDefault(objects.get(8).toString()));
            }
            QueryWrapper<TableDetail> detailQueryWrapper = new QueryWrapper<>();
            detailQueryWrapper.eq("COLUMN_NAME", detail.getColumnName());
            detailQueryWrapper.eq("TABLE_ID", tableIdMap.get(objects.get(0).toString()));
            TableDetail tableDetail = tableDetailService.getOne(detailQueryWrapper);
            if (null == tableDetail) {
                TableDetail addTableDetail = new TableDetail();
                String tableDetailId = DateUtil.generateUUID();
                addTableDetail.setId(tableDetailId);
                addTableDetail.setColumnName(detail.getColumnName());
                addTableDetail.setColumnChineseName(detail.getColumnChineseName());
                addTableDetail.setColumnDescribe(detail.getColumnDescribe());
                addTableDetail.setColumnType(detail.getColumnType());
                addTableDetail.setColumnLength(detail.getColumnLength());
                addTableDetail.setColumnDecimal(detail.getColumnDecimal());
                addTableDetail.setCodeName(detail.getCodeName());
                addTableDetail.setPrimaryKey(detail.getPrimaryKey());
                addTableDetail.setColumnNull(detail.getColumnNull());
                addTableDetail.setColumnDefault(detail.getColumnDefault());
                addTableDetail.setStatus(ReportWorldStatusEnum.REPORT_NORMAL.getCode());
                addTableDetail.setFileName(fileName);
                addTableDetail.setTableId(tableIdMap.get(objects.get(0).toString()));
                addTableDetail.setSequence(k);
                addTableDetail.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                addTableDetail.setAddTime(LocalDateTime.now());
                addTableDetail.setVersion(version);
                tableDetailService.save(addTableDetail);
                // 插入对应的模块，客户，报表信息
                this.insertModularCustomerReport(tableDetailId, fileName, objects, false, tableDetailId, true, map);
                k++;
                continue;
            }
            // 已经存在该字段
            tableDetail.setColumnChineseName(detail.getColumnChineseName());
            tableDetail.setColumnType(detail.getColumnType());
            tableDetail.setColumnLength(detail.getColumnLength());
            tableDetail.setColumnDecimal(detail.getColumnDecimal());
            tableDetail.setCodeName(detail.getCodeName());
            tableDetail.setColumnDescribe(detail.getColumnDescribe());
            tableDetail.setPrimaryKey(detail.getPrimaryKey());
            tableDetail.setColumnNull(detail.getColumnNull());
            tableDetail.setColumnDefault(detail.getColumnDefault());
            tableDetail.setSequence(k);
            tableDetail.setEditTime(LocalDateTime.now());
            tableDetail.setVersion(version);
            tableDetailService.updateById(tableDetail);
            // 插入对应的模块，客户，报表信息
            this.insertModularCustomerReport(tableDetail.getId(), fileName, objects, false, tableDetail.getId(), true, map);
            k++;
        }
        buffer.append("成功处理表明细信息" + (listColumnInfo.size() - 1) + "条").append("\r\n");
    }


    /**
     * 上传文件插入对应表对应的客户，模块，报表信息
     *
     * @param objects
     * @param sign     新增还是修改标识
     * @param tableId  表或者表表明细id
     * @param isDetail 时候表明细
     * @throws Exception
     */
    private void insertModularCustomerReport(String dataId, String fileName, List<Object> objects, boolean sign, String tableId, boolean isDetail, Map<String, String> map) throws Exception {
        // 新增表对应的 模块，客户，报表信息
        String modularId = "", reportId = "", customerId = "", dataDemandBugNum = "";
        if (isDetail) {
            if (null != objects.get(9) && StringUtils.isNotBlank(objects.get(9).toString())) {
                modularId = this.getSignValue("MODULAR", objects.get(9).toString());
            }
            if (null != objects.get(10) && StringUtils.isNotBlank(objects.get(10).toString())) {
                reportId = this.getSignValue("REPORT", objects.get(10).toString());
            }
            if (null != objects.get(11) && StringUtils.isNotBlank(objects.get(11).toString())) {
                customerId = this.getSignValue("CUSTOMER", objects.get(11).toString());
            }
            if (null != objects.get(12) && StringUtils.isNotBlank(objects.get(12).toString())) {
                dataDemandBugNum = objects.get(12).toString();
            }

        } else {
            if (null != objects.get(4) && StringUtils.isNotBlank(objects.get(4).toString())) {
                modularId = this.getSignValue("MODULAR", objects.get(4).toString());
            }
            if (null != objects.get(5) && StringUtils.isNotBlank(objects.get(5).toString())) {
                reportId = this.getSignValue("REPORT", objects.get(5).toString());
            }
            if (null != objects.get(6) && StringUtils.isNotBlank(objects.get(6).toString())) {
                customerId = this.getSignValue("CUSTOMER", objects.get(6).toString());
            }
            if (null != objects.get(7) && StringUtils.isNotBlank(objects.get(7).toString())) {
                dataDemandBugNum = objects.get(12).toString();
            }
        }
        boolean isCount = true;
        String dataCode = isDetail ? ReportWorldEnum.REPORT_TABLE_DETAIL_CODE.getCode() :
                ReportWorldEnum.REPORT_TABLE_CODE.getCode(); // 表信息还是表明细信息
        if (StringUtils.isNotBlank(modularId)) {
            // 修改的数据查询是否存在
            if (sign) {
                QueryWrapper<DataModular> modularQueryWrapper = new QueryWrapper<>();
                modularQueryWrapper.eq("DATA_ID", tableId);
                modularQueryWrapper.eq("MODULAR_ID", modularId);
                modularQueryWrapper.eq("DATA_CODE", dataCode);
                int count = dataModularService.count(modularQueryWrapper);
                if (count > 0) {
                    isCount = false;
                }
            }
            if (isCount) {
                DataModular modular = new DataModular();
                modular.setId(DateUtil.generateUUID());
                modular.setModularId(modularId);
                modular.setDataId(tableId);
                modular.setDataCode(dataCode);
                modular.setFileName(fileName);
                modular.setAddTime(LocalDateTime.now());
                modular.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                dataModularService.save(modular);
            }
        }
        if (StringUtils.isNotBlank(reportId)) {
            isCount = true;
            // 修改的数据查询是否存在
            if (sign) {
                QueryWrapper<DataReport> reportQueryWrapper = new QueryWrapper<>();
                reportQueryWrapper.eq("DATA_ID", tableId);
                reportQueryWrapper.eq("REPORT_ID", reportId);
                reportQueryWrapper.eq("DATA_CODE", dataCode);
                int count = dataReportService.count(reportQueryWrapper);
                if (count > 0) {
                    isCount = false;
                }
            }
            if (isCount) {
                DataReport report = new DataReport();
                report.setId(DateUtil.generateUUID());
                report.setReportId(reportId);
                report.setDataId(tableId);
                report.setDataCode(dataCode);
                report.setFileName(fileName);
                report.setAddTime(LocalDateTime.now());
                report.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                dataReportService.save(report);
            }
        }
        if (StringUtils.isNotBlank(customerId)) {
            isCount = true;
            // 修改的数据查询是否存在
            if (sign) {
                QueryWrapper<DataCustomer> customerQueryWrapper = new QueryWrapper<>();
                customerQueryWrapper.eq("DATA_ID", tableId);
                customerQueryWrapper.eq("CUSTOMER_ID", customerId);
                customerQueryWrapper.eq("DATA_CODE", dataCode);
                int count = dataCustomerService.count(customerQueryWrapper);
                if (count > 0) {
                    isCount = false;
                }
            }
            if (isCount) {
                DataCustomer customer = new DataCustomer();
                customer.setId(DateUtil.generateUUID());
                customer.setCustomerId(customerId);
                customer.setDataId(tableId);
                customer.setDataCode(dataCode);
                customer.setFileName(fileName);
                customer.setAddTime(LocalDateTime.now());
                customer.setAddUser(redisCacheManager.getLoginUserInfo().getLoginId());
                dataCustomerService.save(customer);
            }
        }

        // 修改记录信息
        if (StringUtils.isNotBlank(dataDemandBugNum) && StringUtils.isNotBlank(dataId) && map.containsKey(dataDemandBugNum)) {
            // 修改的数据查询是否存在
            if (sign) {
                QueryWrapper<DataDemandBug> updateInfoQueryWrapper = new QueryWrapper<>();
                updateInfoQueryWrapper.eq("DATA_ID", dataId);
                updateInfoQueryWrapper.eq("DEMAND_BUG_ID", map.get(dataDemandBugNum));
                updateInfoQueryWrapper.eq("DATA_CODE", dataCode);
                int count = dataDemandBugService.count(updateInfoQueryWrapper);
                if (count > 0) {
                    isCount = false;
                }
            }
            if (isCount) {
                DataDemandBug dataDemandBug = new DataDemandBug();
                dataDemandBug.setId(DateUtil.generateUUID());
                dataDemandBug.setDataCode(dataCode);
                dataDemandBug.setFileName(fileName);
                dataDemandBugService.save(dataDemandBug);
            }
        }
    }

    /**
     * execl表格里的模块，客户，报表，转换成库里的id
     *
     * @param key
     * @param value
     * @return
     */
    public String getSignValue(String key, String value) throws Exception {
        if (StringUtils.isBlank(value)) {
            return value;
        }
        // 需求/BUG
        if ("DEMANDBUGTYPE".equals(key)) {
            if ("需求".equals(value)) {
                return "1";
            } else {
                return "2";
            }
        }
        // 所属模块
        if (key.equals("MODULAR")) {
            Map<String, Modular> map = modularService.getModularMap();
            for (Map.Entry<String, Modular> entry : map.entrySet()) {
                if (null == entry || null == entry.getValue()) {
                    continue;
                }
                // execl里的和名字匹配上返回这个id的值
                if (value.equals(entry.getValue().getName())) {
                    return entry.getKey();
                }
            }
            return value;
        }
        // 所属客户
        if (key.equals("CUSTOMER")) {
            Map<String, Customer> map = customerService.getCustomerMap();
            for (Map.Entry<String, Customer> entry : map.entrySet()) {
                if (null == entry || null == entry.getValue()) {
                    continue;
                }
                // execl里的和名字匹配上返回这个id的值
                if (value.equals(entry.getValue().getName())) {
                    return entry.getKey();
                }
            }
            return value;
        }
        // 所属报表
        if (key.equals("REPORT")) {
            Map<String, Report> map = reportService.getReportMap();
            for (Map.Entry<String, Report> entry : map.entrySet()) {
                if (null == entry || null == entry.getValue()) {
                    continue;
                }
                // execl里的和名字匹配上返回这个id的值
                if (value.equals(entry.getValue().getName())) {
                    return entry.getKey();
                }
            }
            return value;
        }
        return value;
    }

    /**
     * 导出execl
     */
    public List<SheetDTO> tableExeclList(String ids) throws Exception {
        List<Table> list = this.queryReportWorldTable(ids);
        if (CollectionUtils.isEmpty(list)) {
            throw new BusinessException("没有获取到表结构信息，请稍后重试！");
        }
        return this.exportExeclInfo(list);
    }

    /**
     * 导出Execl
     *
     * @throws Exception
     */
    public List<SheetDTO> exportExeclInfo(List<Table> list) throws Exception {
        String tableName = "";
        try {
            Map<String, String> stringMap = new HashMap<>();
            List<Table> worldTableList = new ArrayList<>();
            List<TableDetail> worldTableDetailList = new ArrayList<>();
            // 报表世界所在分层
            Map<String, String> mapTableLayered = dicvalueService.getCacheDicValueMap(ReportWorldEnum.REPORT_TABLE_LAYERED.getCode());
            for (Table table : list) {
                if (null == table || null == table.getStatus() || 1 != table.getStatus()) {
                    continue;
                }
                tableName = table.getName();
                table.setStrLayered(mapTableLayered.get(String.valueOf(table.getLayered())));
                worldTableList.add(table);
                if (stringMap.containsKey(table.getName())) {
                    continue;
                }
                List<TableDetail> detailList = table.getListTableDetail();
                if (CollectionUtils.isEmpty(detailList)) {
                    continue;
                }
                for (TableDetail tableDetail : detailList) {
                    if (null == tableDetail || null == tableDetail.getStatus() || 1 != tableDetail.getStatus()) {
                        continue;
                    }
                    String columnType = TableInfoUtil.handleCloum(tableDetail, true);
                    if (StringUtils.isBlank(columnType)) {
                        continue;
                    }
                    tableDetail.setStrColumnType(columnType);
                    tableDetail.setTableName(table.getName());
                    tableDetail.setTableChineseName(table.getChineseName());
                    tableDetail.setStrColumnNull(null != tableDetail.getColumnNull() && 1 == tableDetail.getColumnNull() ? "是" : "否");
                    worldTableDetailList.add(tableDetail);
                }
                stringMap.put(table.getName(), table.getName());
            }
            List<SheetDTO> sheetList = new ArrayList<>();// 创建sheet集合，用来存储sheet
            Map<String, String> tableFieldAndAlias = new LinkedHashMap<>();
            // 设置只导出有别名的字段
            tableFieldAndAlias.put("name", "表名");
            tableFieldAndAlias.put("chineseName", "中文表名");
            tableFieldAndAlias.put("strLayered", "所在分层");
            tableFieldAndAlias.put("description", "说明");
            SheetDTO sheet = new SheetDTO();
            sheet.setFieldAndAlias(tableFieldAndAlias);
            sheet.setSheetName("表信息");
            sheet.setCollection(worldTableList);
            sheetList.add(sheet);
            Map<String, String> detailFieldAndAlias = new LinkedHashMap<>();
            // 设置只导出有别名的字段
            detailFieldAndAlias.put("tableChineseName", "中文表名");
            detailFieldAndAlias.put("tableName", "英文表名");
            detailFieldAndAlias.put("columnName", "字段名称");
            detailFieldAndAlias.put("columnChineseName", "字段中文名");
            detailFieldAndAlias.put("strColumnType", "字段类型");
            detailFieldAndAlias.put("strColumnNull", "可为空");
            detailFieldAndAlias.put("columnDefault", "默认值");
            detailFieldAndAlias.put("codeName", "字典码值");
            SheetDTO sheetDetail = new SheetDTO();
            sheetDetail.setFieldAndAlias(detailFieldAndAlias);
            sheetDetail.setSheetName("表明细信息");
            sheetDetail.setCollection(worldTableDetailList);
            sheetList.add(sheetDetail);
            return sheetList;
        } catch (Exception ex) {
            throw new BusinessException(tableName + "导出文件失败，请稍后重试！" + ex.getMessage());
        }
    }

    /**
     * 导出建表语句
     */
    public String tableCreateJsonInfo(String ids, String ftype) throws Exception {
        List<Table> list = this.queryReportWorldTable(ids);
        if (CollectionUtils.isEmpty(list)) {
            throw new BusinessException("没有获取到表结构信息，请稍后重试！");
        }
        StringBuffer buffer = new StringBuffer();
        for (Table table : list) {
            if (null == table) {
                continue;
            }

            List<TableDetail> detailList = table.getListTableDetail();
            if (CollectionUtils.isEmpty(detailList)) {
                continue;
            }
            buffer.append(TableInfoUtil.creatTable(table, detailList, ftype));
        }
        return buffer.toString();
    }


    /**
     * 表明细信息转换成json所需要的
     *
     * @param detailList
     * @return
     * @throws Exception
     */
    private List<TableDetailVO> getReportWorldTableDetail(List<TableDetail> detailList) throws Exception {
        if (CollectionUtils.isEmpty(detailList)) {
            throw new BusinessException("没有获取到表明细信息，请稍后重试！");
        }
        List<TableDetailVO> detailVoList = new ArrayList<>();
        // 处理明细数据信息
        for (TableDetail detail : detailList) {
            if (null == detail) {
                continue;
            }
            if (!ReportWorldStatusEnum.getUpgradeToolsZipStatus().contains(detail.getStatus())) {
                log.info("升级工具只取正常和已删除状态的数据信息");
                continue;
            }
            TableDetailVO detailVo = new TableDetailVO();
            BeanUtils.copyProperties(detail, detailVo);
            List<String> listCustomer = new ArrayList<>();
            if (!CollectionUtils.isEmpty(detail.getListCustomer())) {
                for (DataCustomer customer : detail.getListCustomer()) {
                    if (null == customer) {
                        continue;
                    }
                    listCustomer.add(customer.getCustomerId());
                }
                detailVo.setListCustomer(listCustomer);
            }
            if (!CollectionUtils.isEmpty(detail.getListModular())) {
                List<String> listModular = new ArrayList<>();
                for (DataModular modular : detail.getListModular()) {
                    if (null == modular) {
                        continue;
                    }
                    listModular.add(modular.getModularId());
                }
                detailVo.setListModular(listModular);
            }
            if (!CollectionUtils.isEmpty(detail.getListReport())) {
                List<String> listReport = new ArrayList<>();
                for (DataReport report : detail.getListReport()) {
                    if (null == report) {
                        continue;
                    }
                    listReport.add(report.getReportId());
                }
                detailVo.setListReport(listReport);
            }
            // 修改记录信息
            if (!CollectionUtils.isEmpty(detail.getListDataDemandBug())) {
                List<String> listTableUpdateInfo = new ArrayList<>();
                for (DataDemandBug dataDemandBug : detail.getListDataDemandBug()) {
                    if (null == dataDemandBug) {
                        continue;
                    }
                    listTableUpdateInfo.add(dataDemandBug.getDemandBugId());
                }
                detailVo.setListDemandBug(listTableUpdateInfo);
            }
            detailVoList.add(detailVo);
        }
        return detailVoList;
    }


    /**
     * 查询表信息，提供给页面导出数据使用，传入id有值就按照选中的导出，没有选择就导出全量的
     */
    private List<Table> queryReportWorldTable(String ids) throws Exception {
        // 表结构信息
        QueryWrapper<Table> tableQueryWrapper = new QueryWrapper<>();
        if (StringUtils.isNotBlank(ids)) {
            List<Table> list = JSONArray.parseArray(ids, Table.class);
            if (!CollectionUtils.isEmpty(list)) {
                tableQueryWrapper.in("ID", list);
            }
        }
        List<Table> tableList = tableService.list(tableQueryWrapper);
        if (CollectionUtils.isEmpty(tableList)) {
            log.error("没有获取到表字段信息！");
            return null;
        }
        // 表字段信息
        QueryWrapper<TableDetail> tableDetailQueryWrapper = new QueryWrapper<>();
        List<TableDetail> tableDetailList = tableDetailService.list(tableDetailQueryWrapper);
        if (CollectionUtils.isEmpty(tableDetailList)) {
            log.error("没有获取到表字段信息！");
            return null;
        }
        // 索引信息
        Map<String, List<TableIndexed>> mapTableIndexed = tableIndexedService.getTableIndexedMap();
        // 表字段处理
        Map<String, List<TableDetail>> mapTableDetail = new HashMap<>();
        for (TableDetail detail : tableDetailList) {
            if (null == detail || StringUtils.isBlank(detail.getTableId())) {
                continue;
            }
            if (mapTableDetail.containsKey(detail.getTableId())) {
                List<TableDetail> list = mapTableDetail.get(detail.getTableId());
                list.add(detail);
                mapTableDetail.put(detail.getTableId(), list);
            } else {
                List<TableDetail> list = new ArrayList<>();
                list.add(detail);
                mapTableDetail.put(detail.getTableId(), list);
            }
        }
        if (CollectionUtils.isEmpty(mapTableDetail)) {
            log.error("没有获取到表字段信息！");
        }
        for (Table table : tableList) {
            if (null == table || StringUtils.isBlank(table.getId())) {
                continue;
            }
            // 表字段信息 没有字段信息的表跳过
            if (!mapTableDetail.containsKey(table.getId()) || CollectionUtils.isEmpty(mapTableDetail.get(table.getId()))) {
                log.error(table.getChineseName() + "没有获取到字段信息！");
                continue;
            }
            table.setListTableDetail(mapTableDetail.get(table.getId()));
            if (!CollectionUtils.isEmpty(mapTableIndexed)) {
                table.setListIndexed(mapTableIndexed.get(table.getId()));
            }
        }
        return tableList;
    }

    private void queryReportWorldTableDetail(List<Table> list) {
        for (Table table : list) {
            if (null == table || StringUtils.isBlank(table.getId())) {
                continue;
            }
            TableDetail tableDetail = new TableDetail();
            tableDetail.setTableId(table.getId());
            // 获取所有表明细信息
            List<TableDetail> detailList = tableDetailMapper.queryReportTableDetail(tableDetail);
            if (CollectionUtils.isEmpty(detailList)) {
                log.error(table.getName() + "没有获取到表明细信息！");
                continue;
            }
            table.setListTableDetail(detailList);
        }
    }

    /**
     * 查看表变更信息
     *
     * @param ids 选择的表以及客户
     * @return
     * @throws Exception
     */
    public Map<String, String> viewUpgradeTableInfo(String ids) throws Exception {
        List<Table> list = JSONArray.parseArray(ids, Table.class);
        if (CollectionUtils.isEmpty(list)) {
            throw new BusinessException("请选择要对比的表！");
        }
        Connection conn = dbConnConfig.getOutputConnection();
        Map<String, String> map = new HashMap<>();
        StringBuffer msgInfo = new StringBuffer();
        // 从库里获取表信息 查询选中的表
        Table table = new Table();
        table.setListTable(list);
        List<Table> listTable = tableMapper.queryReportTable(table);
        this.queryReportWorldTableDetail(listTable);
        if (CollectionUtils.isEmpty(listTable)) {
            throw new BusinessException("获取系统维护的表字段信息失败，请稍后重试！");
        }
        for (Table tableSystem : listTable) {  //循环页面上选择的所有表名
            if (null == tableSystem || StringUtils.isBlank(tableSystem.getName())) {
                continue;
            }
            // 维护的字段信息
            List<TableDetail> systemFileList = tableSystem.getListTableDetail();
            if (CollectionUtils.isEmpty(systemFileList)) {
                msgInfo.append("没有维护表[" + tableSystem.getName() + "]的字段信息，请联系开发人员！").append("\r\n");
                continue;
            }
            // 获取数据库里的表信息
            Table tableDb = this.getDbTableColumnInfo(conn, tableSystem.getName());
            // 数据库没有获取到
            if (null == tableDb || CollectionUtils.isEmpty(tableDb.getListTableDetail())) {
                msgInfo.append("表[" + tableSystem.getName() + "]数据库还没有创建").append("\r\n");
                continue;
            }
            List<TableDetail> dbFieldList = tableDb.getListTableDetail();
            //比对表是否删除或者新创建表
            if (CollectionUtils.isEmpty(dbFieldList) && (null == tableSystem.getStatus() || 5 == tableSystem.getStatus())) {
                msgInfo.append("表[" + tableSystem.getName() + "]已经删除").append("\r\n");
                continue;
            }
            // 把数据库的字段信息放到MAP总
            Map<String, TableDetail> dbColumnInfoMap = new HashMap<>();
            for (TableDetail detail : dbFieldList) {
                dbColumnInfoMap.put(detail.getColumnName(), detail);
            }
            if (null == tableSystem.getStatus() || 5 == tableSystem.getStatus()) {
                msgInfo.append("表[" + tableSystem.getName() + "]已经删除").append("\r\n");
                continue;
            }
            // 循环维护的表结构信息，一个字段一个字段的比较
            for (TableDetail detailSystem : systemFileList) {
                if (null == detailSystem || StringUtils.isBlank(detailSystem.getColumnName())) {
                    continue;
                }
                // 从数据库的你们取出来比对
                TableDetail datailDb = dbColumnInfoMap.get(detailSystem.getColumnName());
                // 数据库有就比对
                contrastFieldInfo(msgInfo, tableSystem.getName(), detailSystem, datailDb);
                continue;
                //个性化字段的检测
               /* if (fieldInfoSpecial(table, detailSystem)) {
                    contrastFieldInfo(msgInfo, msgWarning, table.getName(), detailSystem, datailDb);
                }*/
            }
            // 循环库里的字段和文件里比对，判断是否库里还有文件里已经没有表示删除的字段
            Map<String, TableDetail> systemColumnInfoMap = new HashMap<>();
            for (TableDetail detail : systemFileList) {
                systemColumnInfoMap.put(detail.getColumnName(), detail);
            }
            for (TableDetail detail : dbFieldList) {
                if (null == detail) {
                    continue;
                }
                if (systemColumnInfoMap.containsKey(detail.getColumnName())) {
                    continue;
                }
                msgInfo.append("表[" + tableSystem.getName() + "]数据库中字段[" + detail.getColumnName() + "]已经被删除！").append("\r\n");
            }
            // 索引对比
            contrastIndexedInfo(msgInfo, tableDb, tableSystem);
        }
        map.put("msgInfo", msgInfo.toString());
        return map;
    }

    /**
     * 对比表字段信息，提示到页面
     *
     * @param msgInfo
     * @param tableName
     * @param datailSystem 文件里表的字段信息
     * @param datailDb     数据库里表的字段信息
     * @return
     * @throws Exception
     */
    private void contrastFieldInfo(StringBuffer msgInfo, String tableName, TableDetail datailSystem, TableDetail datailDb) throws Exception {
        try {
            if (null == datailSystem) {
                msgInfo.append("表[" + tableName + "]获取字段信息失败，请联系开发人员！").append("\r\n");
                return;
            }
            // 库里没有该字段
            if (null == datailDb) {
                msgInfo.append("表[" + tableName + "]增加字段[" + datailSystem.getColumnName() + "]").append("\r\n");
                return;
            }
            // 数据库存在，文件里标识已经删除了的字段
            if (null != datailSystem.getStatus() && 5 == datailSystem.getStatus()) {
                msgInfo.append("表[" + tableName + "]字段[" + datailSystem.getColumnName() + "]已经删除！").append("\r\n");
                return;
            }
            //文件里的字段信息和表里的字段信息一致时
            if (datailSystem.equals(datailDb)) {
                //msgInfo.append("表[" + tableName + "]没有调整").append("\r\n");
                return;
            }
            //类型发生变化
            if ((datailSystem.getColumnType() != 11 && datailDb.getColumnType() != 3)
                    && (datailSystem.getColumnType() != 3 && datailDb.getColumnType() != 11)
                    && !datailSystem.getColumnType().equals(datailDb.getColumnType())) {
                msgInfo.append("表[" + tableName + "]调整字段[" + datailSystem.getColumnName() +
                        "]类型原来是[" + TableInfoUtil.handleCloum(datailDb, false) + "],调整位[" + TableInfoUtil.handleCloum(datailSystem, false) + "]！").append("\r\n");
                msgInfo.append("alter table " + tableName + " modify " + datailSystem.getColumnName() + " " + TableInfoUtil.handleCloum(datailSystem, true)
                        + " " + datailSystem.getColumnDefault() + " " + (2 == datailSystem.getColumnNull() ? " NOT NUL" : "")).append(";").append("\r\n");
            }
            //长度减小
            if (null == datailDb.getColumnLength()) {
                datailDb.setColumnLength(0);
            }
            if (null == datailSystem.getColumnLength()) {
                datailSystem.setColumnLength(0);
            }
            if (datailSystem.getColumnLength() < datailDb.getColumnLength()) {
                msgInfo.append("表[" + tableName + "]调整字段[" + datailSystem.getColumnName() +
                        "]长度原来是[" + datailDb.getColumnLength() + "],调整位[" + datailSystem.getColumnLength() + "]！").append("\r\n");
                msgInfo.append("alter table " + tableName + " modify " + datailSystem.getColumnName() + " " + TableInfoUtil.handleCloum(datailSystem, true)
                        + " " + datailSystem.getColumnDefault() + " " + (2 == datailSystem.getColumnNull() ? " NOT NUL" : "")).append(";").append("\r\n");
            }
            //长度增加
            if (datailSystem.getColumnLength() > datailDb.getColumnLength()) {
                msgInfo.append("表[" + tableName + "]调整字段[" + datailSystem.getColumnName() +
                        "]长度原来是[" + datailDb.getColumnLength() + "],调整位[" + datailSystem.getColumnLength() + "]！").append("\r\n");
                msgInfo.append("alter table " + tableName + " modify " + datailSystem.getColumnName() + " " + TableInfoUtil.handleCloum(datailSystem, true)
                        + " " + datailSystem.getColumnDefault() + " " + (1 == datailDb.getColumnNull() && 2 == datailSystem.getColumnNull() ? " NOT NULL" : "")).append(";").append("\r\n");
            }
            //小数类型的精度变化
            if (null == datailDb.getColumnDecimal()) {
                datailDb.setColumnDecimal(0);
            }
            if (null == datailSystem.getColumnDecimal()) {
                datailSystem.setColumnDecimal(0);
            }
            if (datailSystem.getColumnDecimal() > datailDb.getColumnDecimal()) {
                msgInfo.append("表[" + tableName + "]调整字段[" + datailSystem.getColumnName() +
                        "]精度原来是[" + datailDb.getColumnDecimal() + "],调整位[" + datailSystem.getColumnDecimal() + "]！").append("\r\n");
                msgInfo.append("alter table " + tableName + " modify " + datailSystem.getColumnName() + " " + TableInfoUtil.handleCloum(datailSystem, true)
                        + " " + datailSystem.getColumnDefault() + " " + (2 == datailSystem.getColumnNull() ? " NOT NUL" : "")).append(";").append("\r\n");
            }
            if (null != datailDb.getColumnDecimal() && datailSystem.getColumnDecimal() < datailDb.getColumnDecimal()) {
                msgInfo.append("表[" + tableName + "]调整字段[" + datailSystem.getColumnName() +
                        "]精度原来是[" + datailDb.getColumnDecimal() + "],调整位[" + datailSystem.getColumnDecimal() + "]！").append("\r\n");
                msgInfo.append("alter table " + tableName + " modify " + datailSystem.getColumnName() + " " + TableInfoUtil.handleCloum(datailSystem, true)
                        + " " + datailSystem.getColumnDefault() + " " + (2 == datailSystem.getColumnNull() ? " NOT NUL" : "")).append(";").append("\r\n");
            }
            // 由于创建了表之后默认值有些中间有空格
            String fileDefault = "abc";
            String dbDefault = "abc";
            if (StringUtils.isNotBlank(datailSystem.getColumnDefault()) && !"NULL".equals(datailSystem.getColumnDefault().toUpperCase())) {
                fileDefault = datailSystem.getColumnDefault().trim().toUpperCase().replaceAll("'", "").replace(" ", "");
            }
            if (StringUtils.isNotBlank(datailDb.getColumnDefault()) && !"NULL".equals(datailDb.getColumnDefault().toUpperCase())) {
                dbDefault = datailDb.getColumnDefault().trim().toUpperCase().replaceAll("'", "").replace(" ", "");
            }
            if (!fileDefault.equals(dbDefault)) {
                msgInfo.append("表[" + tableName + "]调整字段[" + datailSystem.getColumnName() +
                        "]默认值原来是[" + datailDb.getColumnDefault() + "],调整位[" + datailSystem.getColumnDefault() + "]").append("\r\n");
            }
        } catch (Exception ex) {
            throw new BusinessException("表[" + tableName + "][" + datailSystem.getColumnName() + "]对比差异信息失败，请稍后重试！");
        }
    }

    /**
     * 对比表索引信息，提示到页面
     *
     * @param msgInfo
     * @param tableDb     数据库表信息
     * @param tableSystem 系统维护表信息
     * @return
     * @throws Exception
     */
    private void contrastIndexedInfo(StringBuffer msgInfo, Table tableDb, Table tableSystem) throws Exception {

        List<TableIndexed> indexedListDb = tableDb.getListIndexed();
        // 把数据库的索引信息放到MAP总
        Map<String, TableIndexed> dbIndexedMap = new HashMap<>();
        if (!CollectionUtils.isEmpty(indexedListDb)) {
            for (TableIndexed indexed : indexedListDb) {
                dbIndexedMap.put(indexed.getIndexedName() + "\t" + indexed.getIndexedColumnName(), indexed);
            }
        }
        List<TableIndexed> indexedListSystem = tableSystem.getListIndexed();
        // 把系统维护的索引信息放到MAP总
        Map<String, TableIndexed> systemIndexedMap = new HashMap<>();
        if (!CollectionUtils.isEmpty(indexedListSystem)) {
            for (TableIndexed indexed : indexedListSystem) {
                systemIndexedMap.put(indexed.getIndexedName() + "\t" + indexed.getIndexedColumnName(), indexed);
            }
        }
        // 循环系统维护的
        if (!CollectionUtils.isEmpty(indexedListSystem)) {
            for (TableIndexed indexedSystem : indexedListSystem) {
                // 维护的索引也有这个，就比较索引字段排序方式是否一致
                if (dbIndexedMap.containsKey(indexedSystem.getIndexedName() + "\t" + indexedSystem.getIndexedColumnName())) {
                    //  msgInfo.append("表[" + tableSystem.getName() + "]索引字段[" + indexedSystem.getIndexedColumnName()).append("\r\n");
                    continue;
                }
                // 库里不存在
                msgInfo.append("表[" + tableSystem.getName() + "]增加索引名称[" + indexedSystem.getIndexedName())
                        .append("]索引字段为[" + indexedSystem.getIndexedColumnName()).append("]\r\n");
            }
        }
        // 循环数据库的，找出库里有系统没有维护
        if (!CollectionUtils.isEmpty(indexedListDb)) {
            for (TableIndexed indexedSystem : indexedListDb) {
                // 维护的索引也有这个，就比较索引字段排序方式是否一致
                if (systemIndexedMap.containsKey(indexedSystem.getIndexedName() + "\t" + indexedSystem.getIndexedColumnName())) {
                    continue;
                }
                msgInfo.append("表[" + tableSystem.getName() + "]索引名称[" + indexedSystem.getIndexedName())
                        .append("]索引字段为[" + indexedSystem.getIndexedColumnName()).append("]已经删除").append("\r\n");
            }
        }
    }

    /**
     * 是否个性化字段
     *
     * @param table
     * @param datailFile
     * @throws Exception
     */
    private boolean fieldInfoSpecial(Table table, TableDetail datailFile) throws Exception {
        // 选择的模块信息
        List<String> modular = null;
        if (StringUtils.isNotBlank(table.getModular())) {
            modular = JSONArray.parseArray(table.getModular(), String.class);
        }
        List<String> finalModularStr = modular;
        // 选择的客户信息
        List<String> customer = null;
        if (StringUtils.isNotBlank(table.getCustomer())) {
            customer = JSONArray.parseArray(table.getCustomer(), String.class);
        }
        List<String> finalCustomerStr = customer;
        //选择的报表信息
        List<String> report = null;
        if (StringUtils.isNotBlank(table.getReport())) {
            report = JSONArray.parseArray(table.getReport(), String.class);
        }
        List<String> finalReportStr = report;
        List<DataModular> modularList = datailFile.getListModular();
        List<DataCustomer> customerList = datailFile.getListCustomer();
        List<DataReport> reportList = datailFile.getListReport();

        //判断字段是不是属于特定报表或者特定客户
        if (!CollectionUtils.isEmpty(modularList) && !CollectionUtils.isEmpty(finalModularStr)) {
            List<String> stringList = new ArrayList<>();
            for (DataModular tableModular : modularList) {
                stringList.add(tableModular.getModularId());
            }
            if (!CollectionUtils.isEmpty(stringList.parallelStream().
                    filter(item -> finalModularStr.contains(item)).collect(Collectors.toList()))) {
                return true;
            }
        }
        if (!CollectionUtils.isEmpty(customerList) && !CollectionUtils.isEmpty(finalCustomerStr)) {
            List<String> stringList = new ArrayList<>();
            for (DataCustomer tableCustomer : customerList) {
                stringList.add(tableCustomer.getCustomerId());
            }
            if (!CollectionUtils.isEmpty(stringList.parallelStream().
                    filter(item -> finalCustomerStr.contains(item)).collect(Collectors.toList()))) {
                return true;
            }
        }
        if (!CollectionUtils.isEmpty(reportList) && !CollectionUtils.isEmpty(finalReportStr)) {
            List<String> stringList = new ArrayList<>();
            for (DataReport tableReport : reportList) {
                stringList.add(tableReport.getReportId());
            }
            if (!CollectionUtils.isEmpty(stringList.parallelStream().
                    filter(item -> finalReportStr.contains(item)).collect(Collectors.toList()))) {
                return true;
            }
        }
        return false;
    }
    /**
     * 获取库里表结构信息
     *
     * @param reportWorldExpBean
     * @return
     * @throws Exception
     */
    /**
     * 取得一张表的结构信息
     * 使用DatabaseMetaData与ResultSetMetaData结合的方式获取全部属性
     *
     * @param tableName 表名
     * @return 表结构中列的存储对象
     * @throws SQLException
     */
    public Table getDbTableColumnInfo(Connection conn, String tableName) throws Exception {
        Table reportWorldTable = new Table();
        try {
            TableInfo table = JdbcUtil.getTablesInfo(tableName, conn);
            if (null == table || StringUtils.isBlank(table.getName())) {
                log.info("数据库没有获取到表信息" + tableName);
                return reportWorldTable;
            }
            reportWorldTable.setName(table.getName());
            reportWorldTable.setChineseName(table.getRemarks());
            // 获取索引信息
            List<IndexInfo> listIndex = table.getListIndexInfo();
            if (!CollectionUtils.isEmpty(listIndex)) {
                List<TableIndexed> tableIndexeds = new ArrayList<>();
                for (IndexInfo info : listIndex) {
                    if (null == info) {
                        continue;
                    }
                    TableIndexed worldTableIndexed = new TableIndexed();
                    worldTableIndexed.setIndexedName(info.getIndexName());
                    worldTableIndexed.setIndexedColumnName(info.getColumnName());
                    // 索引列排序
                    String indexAscOrDesc = ("A".equals(info.getAscOrDesc())) ? "ASC" : ("B".equals(info.getAscOrDesc())) ? "DESC" : "";
                    worldTableIndexed.setIndexedAscDesc(indexAscOrDesc);
                    if (StringUtils.isBlank(info.getIndexType())) {
                        worldTableIndexed.setIndexedType(1);
                    } else {
                        worldTableIndexed.setIndexedType(Integer.valueOf(info.getIndexType()));
                    }
                    worldTableIndexed.setIndexedColumnSequence(Integer.valueOf(info.getOrdinalPosition()));
                    tableIndexeds.add(worldTableIndexed);
                }
                reportWorldTable.setListIndexed(tableIndexeds);
            }
            List<ColumnInfo> list = JdbcUtil.getColumnsInfo(tableName, conn);
          /*  Map<String, String> map = new LinkedHashMap<>();
            for (ColumnInfo columnInfo : list) {
                if (null == columnInfo) {
                    continue;
                }
                map.put(columnInfo.getName().toUpperCase(), columnInfo.getRemarks());
            }
            String json = JSONObject.toJSONString(map, SerializerFeature.WriteMapNullValue);
            System.out.println(json);*/
            List<TableDetail> worldTableDetailList = new ArrayList<>();
            for (ColumnInfo columnInfo : list) {
                if (null == columnInfo || StringUtils.isBlank(columnInfo.getName())) {
                    continue;
                }
                worldTableDetailList.add(TableInfoUtil.getReportTableDetail(columnInfo, null));
            }
            reportWorldTable.setListTableDetail(worldTableDetailList);
        } catch (Exception ex) {
            log.error("获取表字段信息出错" + ex.getMessage() + tableName);
        }
        return reportWorldTable;
    }


}
